#!/bin/bash
# Author: zhoubangjun
# 安装mysql8.0.x mgr集群
# 操作系统：CentOS7/银河麒麟v10

set -e
# 使用root用户执行
[[ $(id -u) -ne 0 ]] && echo "请使用root用户执行此脚本" && exit 1

usage(){
    echo "Usage:"
    echo "sh $0
            --prefix=安装目录(默认/data/mysql-\$VERSION)
            --version=MySQL版本(版本需>=8.0)
            --ip_list=集群ip列表,用逗号分割
            --hostname_list=集群内每台主机的hostname名称，用逗号分割，和集群ip列表一一对应
            --uuid=集群UUID(集群内各服务器保持一致)
            --role=primary|secodary(默认secodary)
            --password=集群中用于同步数据的用户密码(集群内各服务器保持一致,默认密码z7529idjVpVmlstb)
        "
    echo "uuid生成方法：cat /proc/sys/kernel/random/uuid"
    echo "示例："
    echo "sh $0 --version=8.3.0 --ip_list=192.168.10.1,192.168.10.2,192.168.10.3 --hostname_list=node1,node2,node3 --uuid=32d4c4f1-a55d-45f9-84f2-dd89fd9db470 --role=primary --password=z7529idjVpVmlstb"
    exit 1
}

#处理参数，规范化参数
ARGS=$(getopt -o h --long prefix:,version:,ip_list:,hostname_list:,uuid:,role:,password:,help -n "$0" -- "$@")

if [[ $? != 0 || $# -eq 0 ]]
then
        usage
fi

#重新排列参数顺序
eval set -- "${ARGS}"
#通过shift和while循环处理参数
while true
do
    case $1 in
        --prefix)
            PREFIX=$2
            shift 2
            ;;
        --version)
            VERSION=$2
            shift 2
            ;;
        --ip_list)
            IP_LIST=$2
            shift 2
            ;;
        --hostname_list)
            HOSTNAME_LIST=$2
            shift 2
            ;;            
        --role)
            ROLE=$2
            shift 2
            ;;
        --uuid)
            UUID=$2
            shift 2
            ;;
        --password)
            PASSWORD=$2
            shift 2
            ;;            
        -h|-help)
            usage
            exit 1
            ;;
        --)
           shift
           break
           ;;
        *)
           echo "Internal error!"
           exit 1
           ;;
    esac
done


# 设置临时变量
VERSION=${VERSION-8.3.0}
VERSION_INT=$(echo "${VERSION}" | sed 's/.//g')
[[ "${VERSION_INT}" -lt 800 ]] && echo "版本号必须>=8.0" && exit 1

MAIN_VERSION=$(echo ${VERSION} | awk -F'.' '{print $1"."$2}')
GLIBC_VERSION=$(getconf GNU_LIBC_VERSION | awk '{print $2}')

INSTALL_DIR="${PREFIX:-/data/mysql-${VERSION}}"
DATA_DIR="${INSTALL_DIR}/data"
RUN_USER="mysql"
HOST_IP=$(ip a | grep global | head -1 | awk -F'/' '{print $1}' | awk '{print $2}')
SERVER_ID=$(echo ${HOST_IP} | awk -F'.' '{print $NF}')
ROLE=${ROLE:-secodary}

# 使用available内存的60%分配innodb_buffer_pool_size
INNODB_BUFFER_POOL_SIZE=$(free -g | sed -n '2p'| awk '{print int($7 * 0.6)}')
[[ ${INNODB_BUFFER_POOL_SIZE} -eq 0 ]] && INNODB_BUFFER_POOL_SIZE=1

# 关闭防火墙和selinux
systemctl stop firewalld && systemctl disable firewalld
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
setenforce  0

# /etc/hosts中添加各主机hostname解析

IFS=',' read -ra ips <<< "${IP_LIST}"
IFS=',' read -ra names <<< "${HOSTNAME_LIST}"

if [ "${#ips[@]}" -ne "${#names[@]}" ]; then
    echo "主机名和ip数量不匹配，无法对应取值"
    exit 1
fi

# 写入/etc/hosts文件 
for ((i=0; i<${#ips[@]}; i++)); do
    ip="${ips[$i]}"
    name="${names[$i]}"
    grep -q "${ip} ${name}" /etc/hosts || echo "$ip $name" >> /etc/hosts 
    GROUP_SEEDS="${name}:33061 ${GROUP_SEEDS}"
done
GROUP_SEEDS="$(echo ${GROUP_SEEDS} | sed 's/ /,/g')"

# 修改系统限制参数
sed -i 's/^*.*soft.*nproc.*/* soft nproc 65535/g' /etc/security/limits.conf /etc/security/limits.d/20-nproc.conf
sed -i 's/^*.*hard.*nproc.*/* hard nproc 65535/g' /etc/security/limits.conf /etc/security/limits.d/20-nproc.conf

# 安装依赖
echo "正在安装依赖包..."
yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget libnuma* bzip2 xz

# 下载安装包
if [[ -f mysql-${VERSION}-linux-glibc${GLIBC_VERSION}-x86_64.tar.xz ]]
then
    echo "正在下载安装包..."
    wget -4 https://cdn.mysql.com/archives/mysql-${MAIN_VERSION}/mysql-${VERSION}-linux-glibc${GLIBC_VERSION}-x86_64.tar.xz
    if [[ $? -ne 0 ]]; then
        echo "mysql-${VERSION}-linux-glibc${GLIBC_VERSION}-x86_64.tar.xz下载失败，请重试或手动上传此安装包"
        exit 1
    fi
fi

echo "正在解压安装包..."
tar Jxf mysql-${VERSION}-linux-glibc${GLIBC_VERSION}-x86_64.tar.xz && mv mysql-${VERSION}-linux-glibc${GLIBC_VERSION}-x86_64 ${INSTALL_DIR}

mkdir -p ${INSTALL_DIR}/logs
mkdir -p ${DATA_DIR}
touch ${INSTALL_DIR}/logs/{slow.log,error.log}

# 创建用户、授权
useradd mysql -s /sbin/nologin
chown -R mysql.mysql ${INSTALL_DIR} ${DATA_DIR}

# 添加环境变量
echo "export PATH=\$PATH:${INSTALL_DIR}/bin/" > /etc/profile.d/mysql.sh
source /etc/profile


# 备份原 /etc/my.cnf
if [ -f /etc/my.cnf ]; then
    mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%m`
fi 

# 添加配置文件my.cnf
# 注意修改server-id和loose-group_replication_local_address
cat >/etc/my.cnf <<EOF
[client]
port = 3306
socket = ${DATA_DIR}/mysql.sock

[mysql]
prompt="\u@\h \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user = ${RUN_USER}
port = 3306
bind-address = ${HOST_IP}
admin_address = 127.0.0.1
basedir = ${INSTALL_DIR}
datadir = ${DATA_DIR}
socket = ${DATA_DIR}/mysql.sock
pid-file = ${DATA_DIR}/mysql.pid
character-set-server = utf8mb4
skip_name_resolve = 1

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
default_authentication_plugin=mysql_native_password

# 集群内server-id不能相同
server-id=${SERVER_ID}
gtid_mode=on
enforce_gtid_consistency=on

# 表名大小写敏感
lower_case_table_names = 0
# 参数优化
open_files_limit = 65535
back_log = 500
max_connections = 1000
thread_cache_size=300
key_buffer_size=256M
table_open_cache=1024
join_buffer_size=16M
sort_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=2M
binlog_cache_size=12M
max_binlog_size=512M
innodb_buffer_pool_size=${INNODB_BUFFER_POOL_SIZE}G
innodb_redo_log_capacity=1073741824 
innodb_log_buffer_size=32M
 
# 控制MySQL磁盘写入策略以及数据安全性
sync_binlog=1
innodb_flush_log_at_trx_commit=1

# binlog
log_bin=mysql-bin
binlog_format=ROW
binlog_checksum=CRC32
max_binlog_size = 1G
binlog_cache_size=8M
relay_log = relay-bin
log_replica_updates=ON
# binlog日志保留7天
binlog_expire_logs_seconds=604800

log_timestamps = SYSTEM
# 审计日志
general_log=1
general_log_file=${INSTALL_DIR}/logs/general.log

# 慢查询日志
slow_query_log=1
long_query_time=3
slow_query_log_file=${INSTALL_DIR}/logs/slow.log
# 错误日志
log-error = ${INSTALL_DIR}/logs/error.log

pid-file = ${DATA_DIR}/mysql.pid

# 集群配置
plugin_load_add='group_replication.so'
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = ${UUID}
# 在部署期间关闭组复制的自启动，可以在部署成功后改为启用
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = "${HOSTNAME}:33061"
# 组内所有节点的主机名或IP:端口号，以逗号分隔。 配置种子成员的主机和端口列表。种子成员是指新连入复制组的新成员。通常配置为所有组成员
loose-group_replication_group_seeds = "${GROUP_SEEDS}"
# 是否自动引导组。此选项只能在一个server实例上使用，通常是首次引导组时(或在整组成员关闭的情况下)，如果多次引导，可能出现脑裂。
loose-group_replication_bootstrap_group = OFF
# 设置自动递增的增量值,值集群中服务器数量
loose-group_replication_auto_increment_increment=3


[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
open-files-limit=65535
EOF

chown -R mysql.mysql /etc/my.cnf
# 初始化
echo "正在初始化数据..."
${INSTALL_DIR}/bin/mysqld --defaults-file=/etc/my.cnf --user=${RUN_USER} --basedir=${INSTALL_DIR} \
--datadir=${DATA_DIR} --initialize-insecure
[[ $? -ne 0 ]] && echo "mysql初始化失败" && exit 1

# 添加mysqld.service，使用systemd管理mysql
cat > /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
After=network.target
After=syslog.target

[Service]
User=${RUN_USER}
Group=${RUN_USER}
ExecStart=${INSTALL_DIR}/bin/mysqld_safe --datadir=${INSTALL_DIR}/data --pid-file=${INSTALL_DIR}/mysqld.pid
Restart=on-failure
RestartSec=30s

[Install]
WantedBy=multi-user.target
EOF

# 启动mysql
systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld
sleep 5
systemctl status mysqld
[[ $? -ne 0 ]] && echo "mysql启动失败" && exit 1

# 生成12个字符长的随机密码
# PASSWORD="$(gpg --gen-random --armor 1 12)"
PASSWORD=${PASSWORD:-z7529idjVpVmlstb}
SQL="
    set sql_log_bin=0;
    CREATE USER 'repl'@'%' identified by '${PASSWORD}';
    GRANT REPLICATION SLAVE,REPLICATION CLIENT on *.* to 'repl'@'%';
    FLUSH PRIVILEGES;
    set sql_log_bin=1;
"
mysql -e"${SQL}";

# 配置mgr
# 在主节点开启组复制引导【仅在主节点执行】
if [[ ${ROLE} == "primary" ]]
then
    SQL="
        SET GLOBAL group_replication_bootstrap_group=ON;
        START GROUP_REPLICATION;
        SELECT * FROM performance_schema.replication_group_members;
        SET GLOBAL group_replication_bootstrap_group=OFF;
    "
    mysql -e"${SQL}"
fi

# 加入其他服务器到复制组【仅在从节点执行】
if [[ ${ROLE} == "secodary" ]]
then
    if [[ ${VERSION_INT} -lt 8022 ]]
    then
        #MySQL 8.0.23版本以前的语法为
        SQL="
            reset master;
            CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='${PASSWORD}' FOR CHANNEL 'group_replication_recovery';
            start group_replication;
        "
    else
        #MySQL 8.0.23版本以后的语法为
        SQL="
            reset master;
            CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='${PASSWORD}' FOR CHANNEL 'group_replication_recovery';
            start group_replication;
        "
    fi
    mysql -e"${SQL}"
fi

# 自动启动组复制
sed -i 's/loose-group_replication_start_on_boot.*/loose-group_replication_start_on_boot = ON/' /etc/my.cnf

# 验证复制组状态,在任何一个服务器上，执行以下 SQL 命令来查看复制组的状态：
echo "当前集群状态如下"
SQL="SELECT * FROM performance_schema.replication_group_members;"
mysql -e"${SQL}"

# 添加集群账号【主节点执行】
if [[ ${ROLE} == "primary" ]]
then
    PASSWORD="$(gpg --gen-random --armor 1 12)"
    SQL="
        CREATE USER 'cluster'@'%' IDENTIFIED BY '${PASSWORD}';
        GRANT all privileges ON *.* TO 'cluster'@'%' WITH GRANT OPTION;
        FLUSH PRIVILEGES;
    "
    mysql -e"${SQL}"
    echo "集群账号信息如下:"
    echo "用户名密码: cluster / ${PASSWORD}"
fi

# 修改root密码
# 生成12个字符长的随机密码
#PASSWORD="$(gpg --gen-random --armor 1 12)"
#SQL="
#    ALTER user 'root'@'localhost' IDENTIFIED BY '${PASSWORD}';
#    FLUSH PRIVILEGES;
#"
#mysql -uroot mysql -e"${SQL}"
#echo "MySQL root账号密码: ${PASSWORD}"

# mysql mgr 切换主节点
# select group_replication_set_as_primary("member_id");
